import pymysql
import pprint


def get_mysql_db(config):
    db = pymysql.connect(host=config.get('host'), user=config.get('user'), password=config.get('password'),
                  database=config.get('database')       )
    return db


def get_prod_db():
    config = {
        'host': 'rm-bp1gp348ln6q8nh5p4o.mysql.rds.aliyuncs.com',
        'user': 'harryli',
        'password': '38424dFb',
        'database': 'rcnprod'

    }
    # 打开数据库连接
    db = get_mysql_db(config)
    # SELECT * FROM INFORMATION_SCHEMA.TABLES
    # db = pymysql.connect(host='rm-bp1gp348ln6q8nh5p4o.mysql.rds.aliyuncs.com',
    #                      user='harryli',
    #                      password='38424dFb',
    #                      database='rcnprod')
    return db


def get_version(cursor):
    # 使用 execute()  方法执行 SQL 查询
    cursor.execute("SELECT VERSION()")
    # 使用 fetchone() 方法获取单条数据.
    data = cursor.fetchone()
    print("Database version : %s " % data)


def get_db_names(cursor, is_dict=True):
    null = None
    colstr = 'TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT'
    # TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT
    cols = colstr.split()
    sql = 'SELECT * FROM INFORMATION_SCHEMA.TABLES'
    cursor.execute(sql)
    data = cursor.fetchall()
    if is_dict:
        new_ds = []
        for d in data:
            new_d = dict()
            for ix, col in enumerate(cols):
                new_d[col] = d[ix]
            new_ds.append(new_d)
        return new_ds
    return data


def get_record_by_jd(cursor):
    cursor.execute(
        'select  id, jd_id, compatibility,resume_id from t_talent_pending_resume where jd_id="2c9207157b9b9199017b9fdf555f06d4"')
    data = cursor.fetchall()
    return [u for u in data]


def run():
    db = get_prod_db()
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    get_version(cursor)
    names = get_db_names(cursor)
    for n in names:
        print(n['TABLE_NAME'])

    # 关闭数据库连接
    db.close()


def run_insert_table(conn,tb):


    cur = conn.cursor()
    cur.execute('''
        INSERT INTO `{}` (`name`) VALUES ('东方青龙'), ('南方朱雀'), ('西方白虎'), ('北方玄武');
        '''.format(tb))
    conn.commit()


def get_condition_sql(condition):

    conditions = []
    for field, v in condition.items():
        if isinstance(v,str):
            v = """'{}'""".format(v)
        conditions.append(f'{field}={v}')
    sql = ' and '.join(conditions)
    print('sql',sql)
    return sql

def create_table(conn,sql):
    cur = conn.cursor()
    cur.execute(sql)

def delete_record(conn,table,condition):

    cur = conn.cursor()
    sql = '''Delete from {} where {} '''.format(table,get_condition_sql(condition))
    print('del',sql)
    cur.execute(sql)
    conn.commit()

def update_record(conn,table,change,condition):
    assert  isinstance(change,dict)
    cur = conn.cursor()
    sql = ''' UPDATE `{}` SET {}  WHERE  {} '''.format(table,get_condition_sql(change),get_condition_sql(condition))
    print('update',sql)
    cur.execute(sql)
    conn.commit()

def check_record(conn,tb):
    cur = conn.cursor()
    cur.execute('''
    SELECT *  FROM `{}`;
    '''.format(tb))
    return (cur.fetchall())



def run3():
    config = {

        'host': '192.168.1.90',
        'password': 'root',
        'user': 'root',
        'database': 'tableau'

    }
    tb_name = 'new_table5'
    db = get_mysql_db(config)
    #run_new_table(db, tb_name)
    #run_insert_table(db,tb_name)
    print('insert')
    check_record(db,tb_name)
    update_record(db, tb_name,{'name':'??'},{'id':3})
    delete_record(db,tb_name,{'id':4})
#run3()